﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;
using KPIS.GERP.GIMS.IDAL;
namespace KPIS.GERP.GIMS.DAL
{
    public sealed class PrePurchaseRequestRouteDAL : IDALPrePurchaseRequestRoute
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public PrePurchaseRequestRouteDAL()
        {

        }

        public string Insert(PrePurchaseRequestRouteInfo info)
        {
            string strSQL;
            DataSet ds;
            int lastSeq = 0;
            string lastNumber = "";
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@icm_ppr_seq", info.InfoPrePurchaseRequest.Seq);
                dbManager.AddParameters(1, "@from_emp_seq", info.InfoFromEmp.Seq);
                dbManager.AddParameters(2, "@to_emp_seq", info.InfoFromEmp.Seq);
                dbManager.AddParameters(3, "@remark", info.Remark);
                dbManager.AddParameters(4, "@order_seq", info.Order);
                dbManager.AddParameters(5, "@upper_icm_ppr_route_seq", info.UpperSeq);
                dbManager.AddParameters(6, "@route_type", info.Type);
                dbManager.AddParameters(7, "@active", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(8, "@route_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(9, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(10, "@created_when", info.InfoRecord.Created_when);
                dbManager.AddParameters(11, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(12, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "insert into icm_pre_purchase_request_route (" +
                            "icm_ppr_route_seq," +
                            "icm_ppr_seq," +
                            "from_emp_seq," +
                            "to_emp_seq," +
                            "remark," +
                            "order_seq," +
                            "upper_icm_ppr_route_seq," +
                            "route_type," +
                            //"active," +
                            "route_status," +
                            "created_by," +
                            "created_when," +
                            "updated_by," +
                            "updated_when)" +
                        " values(" +
                            "@icm_ppr_route_seq," +
                            "@icm_ppr_seq," +
                            "@from_emp_seq," +
                            "@to_emp_seq," +
                            "@remark," +
                            "@order_seq," +
                            "@upper_icm_ppr_route_seq," +
                            "@route_type," +
                            //"@active," +
                            "@route_status," +
                            "@created_by," +
                            "@created_when," +
                            "@updated_by," +
                            "@updated_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                strSQL = "SELECT LAST_INSERT_ID()"
                    + " FROM icm_pre_purchase_request_route";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    lastNumber = ds.Tables[0].Rows[0][0].ToString();
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return lastNumber;
        }

        public void Insert(IList<PrePurchaseRequestRouteInfo> ListInfo)
        {
            string strSQL;
            DataSet ds;
            Nullable<int> lastSeq = null;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(13);

                for (int i = 0; i < ListInfo.Count; ++i)
                {
                    dbManager.AddParameters(0, "@icm_ppr_seq", ListInfo[i].InfoPrePurchaseRequest.Seq);
                    dbManager.AddParameters(1, "@from_emp_seq", ListInfo[i].InfoFromEmp.Seq);
                    dbManager.AddParameters(2, "@to_emp_seq", ListInfo[i].InfoFromEmp.Seq);
                    dbManager.AddParameters(3, "@remark", ListInfo[i].Remark);
                    dbManager.AddParameters(4, "@order_seq", ListInfo[i].Order);
                    dbManager.AddParameters(5, "@upper_icm_ppr_route_seq", (ListInfo[i].Type == "APPROVE")? lastSeq : null);
                    dbManager.AddParameters(6, "@route_type", ListInfo[i].Type);
                    dbManager.AddParameters(7, "@active", SystemInfo._ActiveRecordStatus);
                    dbManager.AddParameters(8, "@route_status", SystemInfo._WorkRecordStatus);
                    dbManager.AddParameters(9, "@created_by", ListInfo[i].InfoRecord.Created_by);
                    dbManager.AddParameters(10, "@created_when", ListInfo[i].InfoRecord.Created_when);
                    dbManager.AddParameters(11, "@updated_by", ListInfo[i].InfoRecord.Updated_by);
                    dbManager.AddParameters(12, "@updated_when", ListInfo[i].InfoRecord.Updated_when);

                    strSQL = "insert into icm_pre_purchase_request_route (" +
                                "icm_ppr_route_seq," +
                                "icm_ppr_seq," +
                                "from_emp_seq," +
                                "to_emp_seq," +
                                "remark," +
                                "order_seq," +
                                "upper_icm_ppr_route_seq," +
                                "route_type," +
                                //"active," +
                                "route_status," +
                                "created_by," +
                                "created_when," +
                                "updated_by," +
                                "updated_when)" +
                            " values(" +
                                "@icm_ppr_route_seq," +
                                "@icm_ppr_seq," +
                                "@from_emp_seq," +
                                "@to_emp_seq," +
                                "@remark," +
                                "@order_seq," +
                                "@upper_icm_ppr_route_seq," +
                                "@route_type," +
                                //"@active," +
                                "@route_status," +
                                "@created_by," +
                                "@created_when," +
                                "@updated_by," +
                                "@updated_when)";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                    strSQL = "SELECT LAST_INSERT_ID()"
                        + " FROM icm_pre_purchase_request_route";
                    ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    }
                }
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Insert(IList<PrePurchaseRequestRouteInfo> ListInfo, int PrePurchaseRequestSeq, int UserId, IDBManager dbManagerTrans)
        {
            string strSQL;
            DataSet ds;
            Nullable<int> lastSeq = null;
            try
            {
                IList<PrePurchaseRequestRouteInfo> OldListInfo = new List<PrePurchaseRequestRouteInfo>();
                OldListInfo = GetPrePurchaseRequestRoute(PrePurchaseRequestSeq, dbManagerTrans);
                CopyToHistory(OldListInfo, UserId, dbManagerTrans);

                dbManagerTrans.CreateParameters(11);

                for (int i = 0; i < ListInfo.Count; ++i)
                {
                    dbManagerTrans.AddParameters(0, "@icm_ppr_seq", ListInfo[i].InfoPrePurchaseRequest.Seq);
                    dbManagerTrans.AddParameters(1, "@from_emp_seq", ListInfo[i].InfoFromEmp.Seq);
                    dbManagerTrans.AddParameters(2, "@to_emp_seq", ListInfo[i].InfoToEmp.Seq);
                    dbManagerTrans.AddParameters(3, "@remark", ListInfo[i].Remark);
                    dbManagerTrans.AddParameters(4, "@order_seq", ListInfo[i].Order);
                    dbManagerTrans.AddParameters(5, "@upper_icm_ppr_route_seq", (ListInfo[i].Type == "APPROVE") ? lastSeq : null);
                    dbManagerTrans.AddParameters(6, "@route_type", ListInfo[i].Type);
                    dbManagerTrans.AddParameters(7, "@active", ListInfo[i].Active);
                    dbManagerTrans.AddParameters(8, "@route_status", ListInfo[i].InfoRouteStatus.Status);
                    dbManagerTrans.AddParameters(9, "@created_by", ListInfo[i].InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(10, "@created_when", ListInfo[i].InfoRecord.Created_when);

                    strSQL = "insert into icm_pre_purchase_request_route (" +
                                "icm_ppr_seq," +
                                "from_emp_seq," +
                                "to_emp_seq," +
                                "remark," +
                                "order_seq," +
                                "upper_icm_ppr_route_seq," +
                                "route_type," +
                                //"active," +
                                "route_status," +
                                "created_by," +
                                "created_when)" +
                            " values(" +
                                "@icm_ppr_seq," +
                                "@from_emp_seq," +
                                "@to_emp_seq," +
                                "@remark," +
                                "@order_seq," +
                                "@upper_icm_ppr_route_seq," +
                                "@route_type," +
                                //"@active," +
                                "@route_status," +
                                "@created_by," +
                                "@created_when)";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                    strSQL = "SELECT LAST_INSERT_ID()"
                        + " FROM icm_pre_purchase_request_route";
                    ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL);
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        lastSeq = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void Update(PrePurchaseRequestRouteInfo info)
        {

        }

        public void DeleteAll(IList<PrePurchaseRequestRouteInfo> ListInfo, IDBManager dbManagerTrans)
        {
            try
            {
                string strSQL;
                string ListSeq = "";
                for (int i = 0; i < ListInfo.Count; ++i)
                {
                    ListSeq += ListInfo[i].Seq + ",";
                }
                ListSeq = ListSeq.Substring(0, ListSeq.Length - 1);

                strSQL = "delete from icm_pre_purchase_request_route where icm_ppr_route_seq in (" + ListSeq + ")";

                dbManagerTrans.ExecuteNonQuery(CommandType.Text,strSQL);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void CopyToHistory(IList<PrePurchaseRequestRouteInfo> ListInfo, int UserId, IDBManager dbManagerTrans)
        {
            try
            {
                PrePurchaseRequestRouteHistoryDAL dalPprRouteHis = new PrePurchaseRequestRouteHistoryDAL();
                if (ListInfo.Count == 0)
                    return;
                int TmpPprSeq = (ListInfo[0].InfoPrePurchaseRequest.Seq == null) ? 0 : (int)ListInfo[0].InfoPrePurchaseRequest.Seq;
                int TmpRevisionNo = dalPprRouteHis.GetNextRevisionNo(dbManagerTrans, TmpPprSeq);
                IList<PrePurchaseRequestRouteHistoryInfo> ListPprRouteHistory = new List<PrePurchaseRequestRouteHistoryInfo>();
                foreach (PrePurchaseRequestRouteInfo current in ListInfo)
                {
                    PrePurchaseRequestRouteHistoryInfo PprRouteHistory = new PrePurchaseRequestRouteHistoryInfo();
                    PprRouteHistory.RevisionNo = TmpRevisionNo;
                    PprRouteHistory.InfoFromEmp = current.InfoFromEmp;
                    PprRouteHistory.InfoToEmp = current.InfoToEmp;
                    PprRouteHistory.Remark = current.Remark;
                    PprRouteHistory.Order = current.Order;
                    PprRouteHistory.UpperSeq = current.UpperSeq;
                    PprRouteHistory.Status = current.Status;
                    PprRouteHistory.Type = current.Type;
                    RecordInfo InfoRecord = new RecordInfo();
                    InfoRecord.Created_by = UserId;
                    InfoRecord.Created_when = DateTime.Now;
                    PprRouteHistory.InfoRecord = InfoRecord;
                    PprRouteHistory.InfoPrePurchaseRequest = current.InfoPrePurchaseRequest;
                    ListPprRouteHistory.Add(PprRouteHistory);
                }
                dalPprRouteHis.Insert(ListPprRouteHistory, dbManagerTrans);
                DeleteAll(ListInfo, dbManagerTrans);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IList<PrePurchaseRequestRouteInfo> GetPrePurchaseRequestRoute(PrePurchaseRequestInfo InfoPpr)
        {
            string strSQL;
            DataSet ds;
            DataRowCollection dr;
            IList<PrePurchaseRequestRouteInfo> InfoList = new List<PrePurchaseRequestRouteInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@icm_ppr_seq", InfoPpr.Seq);

                strSQL = "SELECT r.icm_ppr_route_seq," +
                            "r.icm_ppr_seq," +
                            "r.from_emp_seq," +
                            "r.to_emp_seq," +
                            "r.remark," +
                            "r.order_seq, " +
                            "r.upper_icm_ppr_route_seq," +
                            "r.route_type," +
                            //"r.active," +
                            "rs.route_status_name," +
                            "r.route_status" +
                        " FROM icm_pre_purchase_request_route r" +
                            " inner join sys_route_status rs on r.route_status = rs.route_status" +
                        " WHERE icm_ppr_seq = @icm_ppr_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestRouteInfo info = new PrePurchaseRequestRouteInfo();
                        PrePurchaseRequestInfo InfoPpRoquest = new PrePurchaseRequestInfo();
                        HrmEmployeeInfo InfoFromEmp = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoToEmp = new HrmEmployeeInfo();
                        SysRouteStatusInfo InfoStatusRoute = new SysRouteStatusInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_ppr_route_seq"].ToString());

                        InfoPpRoquest.Seq = Convert.ToInt32(dr[i]["icm_ppr_seq"].ToString());
                        info.InfoPrePurchaseRequest = InfoPpRoquest;

                        InfoFromEmp.Seq = Convert.ToInt32(dr[i]["from_emp_seq"].ToString());
                        //InfoFromEmp.Name = (dr[i]["from_emp"]).ToString();
                        info.InfoFromEmp = InfoFromEmp;

                        infoToEmp.Seq = Convert.ToInt32(dr[i]["to_emp_seq"].ToString());
                        //infoToEmp.Name = (dr[i]["to_emp"]).ToString();
                        info.InfoToEmp = infoToEmp;

                        info.Type = (dr[i]["route_type"]).ToString();

                        info.Remark = (dr[i]["remark"]).ToString();

                        info.Order = (Convert.IsDBNull(dr[i]["order_seq"]) ? null : (Nullable<int>)(dr[i]["order_seq"]));

                        info.UpperSeq = (Convert.IsDBNull(dr[i]["upper_icm_ppr_route_seq"]) ? null : (Nullable<int>)(dr[i]["upper_icm_ppr_route_seq"]));

                        InfoStatusRoute.Name = (dr[i]["route_status_name"]).ToString();
                        info.InfoRouteStatus = InfoStatusRoute;

                        info.Status = (dr[i]["route_status"]).ToString();
                        
                        InfoList.Add(info);
                    }
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return InfoList;
        }

        public IList<PrePurchaseRequestRouteInfo> GetPrePurchaseRequestRoute(int PrePurchaseRequestSeq, IDBManager dbManagerTrans)
        {
            string strSQL;
            DataSet ds;
            DataRowCollection dr;
            IList<PrePurchaseRequestRouteInfo> InfoList = new List<PrePurchaseRequestRouteInfo>();
            try
            {
                dbManagerTrans.CreateParameters(1);

                dbManagerTrans.AddParameters(0, "@icm_ppr_seq", PrePurchaseRequestSeq);

                strSQL = "SELECT r.icm_ppr_route_seq," +
                            "r.icm_ppr_seq," +
                            "r.from_emp_seq," +
                            "r.to_emp_seq," +
                            "r.remark," +
                            "r.order_seq, " +
                            "r.upper_icm_ppr_route_seq," +
                            "r.route_type," +
                            //"r.active," +
                            "rs.route_status_name," +
                            "r.route_status" +
                        " FROM icm_pre_purchase_request_route r" +
                            " inner join sys_route_status rs on r.route_status = rs.route_status" +
                        " WHERE icm_ppr_seq = @icm_ppr_seq";

                ds = dbManagerTrans.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PrePurchaseRequestRouteInfo info = new PrePurchaseRequestRouteInfo();
                        PrePurchaseRequestInfo InfoPpRoquest = new PrePurchaseRequestInfo();
                        HrmEmployeeInfo InfoFromEmp = new HrmEmployeeInfo();
                        HrmEmployeeInfo infoToEmp = new HrmEmployeeInfo();
                        SysRouteStatusInfo InfoStatusRoute = new SysRouteStatusInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_ppr_route_seq"].ToString());

                        InfoPpRoquest.Seq = Convert.ToInt32(dr[i]["icm_ppr_seq"].ToString());
                        info.InfoPrePurchaseRequest = InfoPpRoquest;

                        InfoFromEmp.Seq = Convert.ToInt32(dr[i]["from_emp_seq"].ToString());
                        //InfoFromEmp.Name = (dr[i]["from_emp"]).ToString();
                        info.InfoFromEmp = InfoFromEmp;

                        infoToEmp.Seq = Convert.ToInt32(dr[i]["to_emp_seq"].ToString());
                        //infoToEmp.Name = (dr[i]["to_emp"]).ToString();
                        info.InfoToEmp = infoToEmp;

                        info.Type = (dr[i]["route_type"]).ToString();

                        info.Remark = (dr[i]["remark"]).ToString();

                        info.Order = (Convert.IsDBNull(dr[i]["order_seq"]) ? null : (Nullable<int>)(dr[i]["order_seq"]));

                        info.UpperSeq = (Convert.IsDBNull(dr[i]["upper_icm_ppr_route_seq"]) ? null : (Nullable<int>)(dr[i]["upper_icm_ppr_route_seq"]));

                        InfoStatusRoute.Name = (dr[i]["route_status_name"]).ToString();
                        info.InfoRouteStatus = InfoStatusRoute;

                        info.Status = (dr[i]["route_status"]).ToString();

                        InfoList.Add(info);
                    }
                }
                return InfoList;
            }
            catch (Exception ex)
            {
               throw ex;
            }
        }
    }
}
